Excel BI - Excel Challenge 834

excel-challenges

excel-formulas

🔰 Group the alphabets row wise and sum the values beneath them.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 834

Challenge Description

🔰 Group the alphabets row wise and sum the values beneath them.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/800-899/834/834 Group Rowwise.xlsx"
input = read_excel(path, range = "A2:F10")
test  = read_excel(path, range = "H2:I6")

result = input %>%
  as.matrix() %>%
  apply(1, function(x) {
    if (which(input == x[1], arr.ind = TRUE)[1, "row"] %% 2 == 0) {
      paste(x %>% na.omit(), collapse = "+")
    } else {
      paste(x %>% na.omit(), collapse = "")
    }
  }) %>%
  matrix(ncol = 2, byrow = TRUE) %>%
  as_tibble() %>%
  mutate(V2 = map_int(V2, ~ eval(parse(text = .x)))) %>%
  set_names(c("Group", "Sum"))

all.equal(result, test)
# [1] TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns.
Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

import pandas as pd
import numpy as np

path = "800-899/834/834 Group Rowwise.xlsx"
input = pd.read_excel(path, usecols="A:F", skiprows=1, nrows=9)
test = pd.read_excel(path, usecols="H:I", skiprows=1, nrows=4)
input_matrix = input.to_numpy()
result = pd.DataFrame({
    "Group": ["".join([str(x) for x in row if pd.notna(x)]) for row in input_matrix[::2]],
    "Sum": [eval("+".join([str(x) for x in row if pd.notna(x)])) for row in input_matrix[1::2]]
})

print(result.equals(test))
# True

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.